package ua.kharkov.khpi.tarasenko.bach.dao.derby.order;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import ua.kharkov.khpi.tarasenko.bach.core.Constant;
import ua.kharkov.khpi.tarasenko.bach.core.db.DBUtils;
import ua.kharkov.khpi.tarasenko.bach.core.db.EShopDataStoreException;
import ua.kharkov.khpi.tarasenko.bach.dao.core.order.OrderItemDAO;
import ua.kharkov.khpi.tarasenko.bach.dao.core.order.OrderItemDTO;

/**
 * DAO for working with table with items in orders in Derby DB.
 * 
 * @author oleksandr
 * 
 */
public class DerbyOrderItemDAO implements OrderItemDAO {
	private static final Logger log = Logger.getLogger(DerbyOrderItemDAO.class);

	// ===========================================================================
	// Tables column's names
	// ===========================================================================
	private static final String COLUMN_ORDER_ID = "order_id";
	private static final String COLUMN_ITEM_ID = "item_id";
	private static final String COLUMN_ITEM_QUANTITY = "item_quantity";

	// ===========================================================================
	// QUERIES
	// ===========================================================================
	private static final String QUERY_ADD_ITEM_TO_ORDER = "INSERT INTO "
			+ Constant.TABLE_ORDER_ITEM + "(" + COLUMN_ORDER_ID + ", "
			+ COLUMN_ITEM_ID + ", " + COLUMN_ITEM_QUANTITY + ") VALUES(?,?,?)";

	private static final String QUERY_FIND_ITEMS_IN_ORDER = "SELECT * FROM "
			+ Constant.TABLE_ORDER_ITEM + " WHERE " + COLUMN_ORDER_ID + "=?";

	private Connection conn;

	public DerbyOrderItemDAO(Connection conn) {
		this.conn = conn;
	}

	@Override
	public int addItemToOrder(OrderItemDTO orderItemDTO)
			throws EShopDataStoreException {
		if (log.isTraceEnabled()) {
			log.trace("Order-item to add is:\n" + orderItemDTO);
		}

		PreparedStatement ps = null;
		int rowsAdded = 0;
		try {
			ps = conn.prepareStatement(QUERY_ADD_ITEM_TO_ORDER);
			ps.setInt(1, orderItemDTO.getOrderId());
			ps.setInt(2, orderItemDTO.getItemId());
			ps.setInt(3, orderItemDTO.getItemQuantity());

			rowsAdded = ps.executeUpdate();
			if (log.isTraceEnabled()) {
				log.trace("Created " + rowsAdded + " orders.");
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during adding item to order:\n"
							+ orderItemDTO, e);
		} finally {
			DBUtils.close(null, ps);
		}

		return rowsAdded;
	}

	@Override
	public ArrayList<OrderItemDTO> getItemsInOrder(int orderId)
			throws EShopDataStoreException {

		ArrayList<OrderItemDTO> items = new ArrayList<OrderItemDTO>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(QUERY_FIND_ITEMS_IN_ORDER);
			ps.setInt(1, orderId);
			rs = ps.executeQuery();
			OrderItemDTO orderItemDTO = null;
			while (rs.next()) {
				orderItemDTO = extractOrderItem(rs);
				items.add(orderItemDTO);
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during looking for user's orders with id="
							+ orderId, e);
		} finally {
			DBUtils.close(rs, ps);
		}
		return items;
	}

	private OrderItemDTO extractOrderItem(ResultSet rs) throws SQLException {
		OrderItemDTO orderItemDTO = new OrderItemDTO();
		orderItemDTO.setOrderId(rs.getInt(COLUMN_ORDER_ID));
		orderItemDTO.setItemId(rs.getInt(COLUMN_ITEM_ID));
		orderItemDTO.setItemQuantity(rs.getInt(COLUMN_ITEM_QUANTITY));
		return orderItemDTO;
	}
}
